package com.ma4567.remotecontroller.MysqlHelper;

import com.ma4567.remotecontroller.UserManager.User;
import com.ma4567.remotecontroller.util.Matters;
import com.ma4567.remotecontroller.util.Note;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Notes {
    public static void Init() throws SQLException {
        Connection conn = ConnectPool.getConnection();
        PreparedStatement stmt = conn.prepareStatement(
                "create table if not exists notes(" +
                        "    id      int auto_increment primary key," +
                        "    user    int not null," +
                        "    title   text null," +
                        "    `index` text null," +
                        "    time    text null," +
                        "    constraint notes_user_Account_fk" +
                        "    foreign key (user) references user (Account))" +
                        "    character set utf8 engine InnoDB;");
        stmt.executeUpdate();
        conn.close();
    }

    public static ArrayList<Note> getNotesByUser(int user) throws SQLException {
        Connection conn = ConnectPool.getConnection();
        ArrayList<Note> result = new ArrayList<>();

        PreparedStatement stmt = conn.prepareStatement("select * from notes where user = ?;");
        stmt.setInt(1, user);

        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            result.add(new Note(rs.getString("title"), rs.getString("time"), rs.getString("index"), rs.getInt("id"))) ;
        }

        conn.close();
        return result;
    }

    public static void addNote(int user, String time, String title, String index) throws SQLException {
        Connection conn = ConnectPool.getConnection();

        PreparedStatement stmt = conn.prepareStatement("insert into notes (user, time, title, `index`) values (?,?,?,?);");
        stmt.setInt(1, user);
        stmt.setString(2, time);
        stmt.setString(3, title);
        stmt.setString(4, index);

        stmt.executeUpdate();

        conn.close();
    }

    public static void deleteNote(int id) throws SQLException {
        Connection conn = ConnectPool.getConnection();

        PreparedStatement stmt = conn.prepareStatement("delete from notes where id = ?;");
        stmt.setInt(1, id);

        stmt.executeUpdate();

        conn.close();
    }

    public static void updateNote(int id, String time, String title, String index) throws SQLException {
        Connection conn = ConnectPool.getConnection();

        PreparedStatement stmt = conn.prepareStatement("update notes set time = ?, title = ?, `index` = ? where id = ?;");
        stmt.setString(1, time);
        stmt.setString(2, title);
        stmt.setString(3, index);
        stmt.setInt(4, id);

        stmt.executeUpdate();

        conn.close();
    }
}
